using Daemon.Model;
using Daemon.Common.Helpers;
using System;
using System.Collections.Generic;
namespace Daemon.Common.Database
{
    public static class DatabaseHelper
    {
        public static TableInfo GetTableInfo(string tableName, string databaseName, ApiDBContent context)
        {
            var sqlStr = $"select COL.COLUMN_NAME as ColumnName,COL.COLUMN_TYPE as FieldType,COL.COLUMN_COMMENT as Comment,TB.TABLE_COMMENT as TableComment from INFORMATION_SCHEMA.COLUMNS COL,INFORMATION_SCHEMA.TABLES TB where TB.table_name='{tableName}' and TB.TABLE_SCHEMA='{databaseName}' and TB.TABLE_SCHEMA = COL.TABLE_SCHEMA AND TB.TABLE_NAME = COL.TABLE_NAME";
            var result = context.RawSqlQuery<ColumnInfo>(sqlStr, null, r => new ColumnInfo()
            {
                FieldName = string.IsNullOrEmpty(r["ColumnName"]?.ToString()) ? "" : r["ColumnName"]?.ToString(),
                FieldType = string.IsNullOrEmpty(r["FieldType"]?.ToString()) ? "" : r["FieldType"]?.ToString(),
                FieldComment = string.IsNullOrEmpty(r["Comment"]?.ToString()) ? "" : r["Comment"]?.ToString(),
            }, r => new List<string>() { r["TableComment"]?.ToString() });

            string primaryKeySql = $"SELECT COLUMN_NAME as ColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{tableName}' AND CONSTRAINT_NAME='PRIMARY'";
            var primaryKey = context.RawSqlQuery<string>(primaryKeySql, null, r => r["ColumnName"] is DBNull ? null : r["ColumnName"].ToString(), null);
            var tableInfo = new TableInfo() { ColumnInfos = result.entities, TableName = tableName, TableComment = result.extraResult[0], PrimaryKey = primaryKey.entities[0] };
            return tableInfo;
        }

        public static List<TableInfo> GetTableList(string databaseName, ApiDBContent context)
        {
            string sqlStr = $"SELECT TB.TABLE_NAME as TableName,TB.TABLE_COMMENT as TableComment  FROM  INFORMATION_SCHEMA.TABLES TB Where TB.TABLE_SCHEMA ='{databaseName}'";
            var result = context.RawSqlQuery<TableInfo>(sqlStr, null, r => new TableInfo()
            {
                TableName = r["TableName"]?.ToString(),
                TableComment = r["TableComment"]?.ToString(),
            }, null);

            return result.entities;
        }

        public static string GetConnectionString(string ip, int port, string userName, string password, string databaseName)
        {
            return $"Server={ip};port={port};Database={databaseName};uid={userName};pwd={password};pooling=true;CharSet=utf8";
        }
    }
}